package com.roc.web.action.negotiation;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

public class NegotiationSearchCondition extends BaseSearchCondition {

	/**
	 * @Fields serialVersionUID : TODO
	 */
	    
	private static final long serialVersionUID = 1L;
	// 交易起始日期
	private String minDate;
	private String maxDate;
	// 交易金额范围
	private String minRMB;
	private String maxRMB;
	// 商户号
	private String merNo;
    // 操作员
	private String opid;
    // 商户名
	private String merName;

	// 交易账号
	private String account;

	// 交易状态（交易应答码）
	private String reversal;

	private String obj;

	private String orgCode;

	private String respCodeArray;

	private String queryFlag;// 查询标志 1代表风险查询

	private String tranDate; // 交易日期
	
	private String roleType;//角色类型
	
	private String marketorId;//销售人员ID
	
	private String ucbccdArea;

	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {

		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();

		// 交易时间
		if (Constance.isNotEmpty(minDate)) {
			filedBeanList.add(new WhereHQLFieldBean("n.Rec_Crt_Ts >= to_date('"+minDate.trim()+"', 'yyyymmdd')"));
		}
		
		if(Constance.isNotEmpty(maxDate)){
			filedBeanList.add(new WhereHQLFieldBean("n.Rec_Crt_Ts < to_date('"+maxDate.trim()+"', 'yyyymmdd' )+1 "));
       	}
		
		// 交易金额
		if (Constance.isNotEmpty(minRMB) && Constance.isNotEmpty(maxRMB)) {
			
			BigDecimal bigMin = new BigDecimal(minRMB);
			bigMin = bigMin.multiply(new BigDecimal(100));
			BigDecimal bigMax = new BigDecimal(maxRMB);
			bigMax = bigMax.multiply(new BigDecimal(100));
			
			
			filedBeanList.add(new WhereHQLFieldBean("TO_number(replace(N.TRAN_AMT, '-', ''))>="+bigMin.intValue()));
			filedBeanList.add(new WhereHQLFieldBean("TO_number(replace(N.TRAN_AMT, '-', ''))<="+bigMax.intValue()));
		}
		// 商户号
		if (Constance.isNotEmpty(merNo)) {
			filedBeanList.add(new WhereHQLFieldBean("N.MCHNT_CD", merNo.trim(),
					"=", false));
		}
		// 业务号
                if (Constance.isNotEmpty(opid)) {
                        filedBeanList.add(new WhereHQLFieldBean("t.rec_upd_opr", opid.trim(),
                                        "=", false));
                }
		// 交易账号（收款账号）
		if (Constance.isNotEmpty(account)) {
			filedBeanList.add(new WhereHQLFieldBean("N.PYE_ACCT_NO", account.trim(), "=", false));
		}
		// 商户名称
		if (Constance.isNotEmpty(merName)) {
			filedBeanList.add(new WhereHQLFieldBean("T.NAME", merName.trim(), "LIKE",false,"ALL"));
		}
        //交易状态（交易应答码）
		if (Constance.isNotEmpty(reversal)) {         
			if ("99".equals(reversal)) {				
			}
			else if ("0".equals(reversal)) {
				filedBeanList.add(new WhereHQLFieldBean("(N.RESP_CD ='00' or N.RESP_CD='G9' ) " ));
			}
			else if ("1".equals(reversal)){	
				filedBeanList.add(new WhereHQLFieldBean("(N.RESP_CD= '' or N.RESP_CD is null or ( N.RESP_CD != '00' and N.RESP_CD != 'G9' ) )"));
			}
		}
		//所属机构
		if (Constance.isNotEmpty(orgCode)) {
			filedBeanList.add(new WhereHQLFieldBean(" T.BANK_BELONG_CD", orgCode.trim(), "=", false));
		}
		if(Constance.isNotEmpty(ucbccdArea)){//运维人员
			filedBeanList.add(new WhereHQLFieldBean("t.uc_bc_cd_area", ucbccdArea.trim(), "=", false));
		}
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer sb = new StringBuffer();

		//counthql = 
		sb.append("SELECT count(1)  FROM TBL_TFR_AGT_LOG N , TBL_MCHT_INF T ");
				if(roleType.equals(Role.AGENT.getCode() )||roleType.equals(Role.SFB_AGENT.getCode() )){
					sb.append(	  " , tbl_mer_agent_relation r "  );
					
					/*sb.append(	  " and EXISTS (SELECT 1 "  );
					sb.append(     "         FROM T_OPERATORS T1 " );
					sb.append("    where t1.id=t.ext1 ");
					sb.append("       START WITH T1.ID = "+obj+" " );
				    sb.append("              AND T1.ROLE_TYPE = '03' " );
					sb.append("     CONNECT BY PRIOR T1.ID = T1.SUPRI_ID " );
					sb.append("       )  "  );*/
				}
				
				sb.append( " WHERE t.mcht_cd=N.MCHNT_CD  and N.ma_trans_cd<>'5091'   ");
//						+ "  AND Translate(t.mcht_cd USING CHAR_CS)=N.MCHNT_CD and N.ma_trans_cd<>'5091'  ");
				if(roleType.equals(Role.AGENT.getCode() )||roleType.equals(Role.SFB_AGENT.getCode() )){ 
					sb.append(" and r.agentid = '" + obj + "' " );
					sb.append(" and N.MCHNT_CD = r.merno " );
				}
				if(roleType.equals(Role.MARKET_DIRECT.getCode()) || roleType.equals(Role.MARKET_LEAD.getCode())){
					sb.append(" and exists (select 1 from t_oper_role tor where T.uc_bc_cd_area = tor.agentid and tor.operid ="+marketorId+") ");
				}

		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb = new StringBuffer();
		
		        sb.append(  "select N.TRANS_DT,N.TRANS_TM,N.RESP_CD,N.TRAN_SEQ,N.MCHNT_CD,T.NAME,N.PYE_BANK_NM,N.PYE_ACCT_NO,N.PYE_ACCT_NM,N.PRI_ACCT_NO,N.PRI_ACCT_NM,N.TRAN_AMT,N.STLM_FEE,N.STLM_AMT  "

				              + "  from TBL_TFR_AGT_LOG  N,TBL_MCHT_INF T  ");
		        if(roleType.equals(Role.AGENT.getCode() )||roleType.equals(Role.SFB_AGENT.getCode() )){
		        	sb.append(	  " , tbl_mer_agent_relation r "  );        	
		        	/*sb.append(	  " and EXISTS (SELECT 1 "  );
					sb.append(     "         FROM T_OPERATORS T1 " );
					sb.append("    where t1.id=t.ext1 ");
					sb.append("       START WITH T1.ID = "+obj+" " );
				    sb.append("              AND T1.ROLE_TYPE = '03' " );
					sb.append("     CONNECT BY PRIOR T1.ID = T1.SUPRI_ID " );
					sb.append("       )  "  );*/
				}
		        
				sb.append(   " where t.mcht_cd=N.MCHNT_CD  and N.ma_trans_cd<>'5091'   ");
//						+ " AND Translate(t.mcht_cd USING CHAR_CS)=N.MCHNT_CD and N.ma_trans_cd<>'5091' "   );
				
				if(roleType.equals(Role.AGENT.getCode() )||roleType.equals(Role.SFB_AGENT.getCode() )){ 
					sb.append("  and r.agentid = '" + obj + "'  " );
					sb.append("  and N.MCHNT_CD = r.merno " );
				}
				if(roleType.equals(Role.MARKET_DIRECT.getCode()) || roleType.equals(Role.MARKET_LEAD.getCode())){
					sb.append(" and exists (select 1 from t_oper_role tor where T.uc_bc_cd_area = tor.agentid and tor.operid ="+marketorId+") ");
				}
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {

		return " ";
	}

	/**
	 * 获取查询条件 excel显示标题
	 * 
	 * @return
	 */
	public List<String> getConditions() {
		List<String> list = new ArrayList<String>();
		list.add("报表日期：");
		list.add(Constance.formateDate());
		// 交易时间
		if (Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) {
			list.add("交易时间:");
			list.add(minDate + "-" + maxDate);
		}
		// 交易金额
		if (Constance.isNotEmpty(minRMB) && Constance.isNotEmpty(maxRMB)) {
			list.add("交易金额:");
			list.add(minRMB + "-" + maxRMB);
		}
		// 商户号
		if (Constance.isNotEmpty(merNo)) {
			list.add("商户号:");
			list.add(merNo);
		}
		// 交易账号（收款账号）
		if (Constance.isNotEmpty(account)) {
			list.add("交易账号：");
			list.add(account);
		}
		// 商户名称
		if (Constance.isNotEmpty(merName)) {
			list.add("商户名称");
			list.add(merName);
		}
		return list;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public String getMinRMB() {
		return minRMB;
	}

	public void setMinRMB(String minRMB) {
		this.minRMB = minRMB;
	}

	public String getMaxRMB() {
		return maxRMB;
	}

	public void setMaxRMB(String maxRMB) {
		this.maxRMB = maxRMB;
	}

	public String getMerNo() {
		return merNo;
	}

	public void setMerNo(String merNo) {
		this.merNo = merNo;
	}

	public String getMerName() {
		return merName;
	}

	public void setMerName(String merName) {
		this.merName = merName;
	}

	public String getAccount() {
		return account;
	}

	public void setAccount(String account) {
		this.account = account;
	}

	public String getReversal() {
		return reversal;
	}

	public void setReversal(String reversal) {
		this.reversal = reversal;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getRespCodeArray() {
		return respCodeArray;
	}

	public void setRespCodeArray(String respCodeArray) {
		this.respCodeArray = respCodeArray;
	}

	public String getQueryFlag() {
		return queryFlag;
	}

	public void setQueryFlag(String queryFlag) {
		this.queryFlag = queryFlag;
	}

	public String getTranDate() {
		return tranDate;
	}

	public void setTranDate(String tranDate) {
		this.tranDate = tranDate;
	}
    public String getOpid() {
	        return opid;
	}

	public void setOpid(String opid) {
	        this.opid = opid;
    }

	public String getRoleType() {
		return roleType;
	}

	public void setRoleType(String roleType) {
		this.roleType = roleType;
	}

	public String getMarketorId() {
		return marketorId;
	}

	public void setMarketorId(String marketorId) {
		this.marketorId = marketorId;
	}

	public String getUcbccdArea() {
		return ucbccdArea;
	}

	public void setUcbccdArea(String ucbccdArea) {
		this.ucbccdArea = ucbccdArea;
	}


}
